﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using DatabaseExtension.ConnectionString;


namespace DatabaseExtension
{
    public sealed class DbInterface : IDisposable
    {
        /// <summary>
        /// Descript which additial db action can be used.
        /// </summary>
        public DbInterfaceActions Actions
        {
            get;
            set;
        }
        /// <summary>
        /// Get or Set the Maximum amount of parameters in the Collection.
        /// </summary>
        public int MaxParamaterSize = 1000;
        /// <summary>
        /// Set the execute Reader behavior
        /// </summary>
        public System.Data.CommandBehavior ExecuteReaderBehavior;
        /// <summary>
        /// Database Interface Object disposed.
        /// </summary>
        public Boolean Disposed
        {
            get;
            private set;
        }
        /// <summary>
        /// TRUE => DBConnection has been initialized
        /// </summary>
        private Boolean _ConnectionSet;
        /// <summary>
        /// True if transaction started
        /// </summary>
        private Boolean _IsTransStarted;
        /// <summary>
        /// Database Configuration Data
        /// </summary>
        private ConnectionInfo _Configurations;
        #region Sql Connections
        /// <summary>
        /// Create DB Connection instance depend on what kind of DB has been selected
        /// </summary>
        private DbConnection Connection
        {
            get
            {
                if (this._Connection == null || this._ConnectionSet == false)
                {
                    this._Connection = this._Configurations.ClientFactory.CreateConnection();
                    this._Connection.ConnectionString = this._Configurations.ConnectionString;
                    this._ConnectionSet = true;
                }
                if (this.Actions.HasFlag(DbInterfaceActions.AutoOpen) == true)
                    this.Connect();
                return this._Connection;
            }
        }
        private DbConnection _Connection;
        /// <summary>
        /// Close current connection, without disposing it.
        /// </summary>
        public void Close()
        {
            if (this.Connection != null)
                this.Connection.Close();
        }
        /// <summary>
        /// Connect to the Database if no connection exists or connection is broken.
        /// </summary>
        public void Connect()
        {
            if (this._Connection != null && (this._Connection.State == (System.Data.ConnectionState.Broken) || this._Connection.State == (System.Data.ConnectionState.Closed)))
                this._Connection.Open();
        }
        #endregion
        #region Sql Command
        /// <summary>
        /// Get the correct DB Command for SQL Server
        /// </summary>
        private DbCommand Command
        {
            get
            {
                if (this._Command == null)
                {
                    this._Command = this._Configurations.ClientFactory.CreateCommand();
                    this._Command.Connection = this.Connection;
                }
                return this._Command;
            }
        }
        private DbCommand _Command;
        #endregion
        #region Transactions
        /// <summary>
        /// Start Read Uncommmitted Tranasaction
        /// </summary>
        /// <returns>Transaction Information</returns>
        /// <exception cref="ArgumentException"></exception>
        /// <exception cref="ArgumentNullException"></exception>
        public DbTransaction BeginTransaction()
        {
            return this.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
        }
        /// <summary>
        /// Start Tranasction with specific Level
        /// </summary>
        /// <param name="level">Transaction Isolation Level.</param>
        /// <returns>Transaction Information</returns>
        /// <exception cref="ArgumentException"></exception>
        /// <exception cref="ArgumentNullException"></exception>
        public DbTransaction BeginTransaction(System.Data.IsolationLevel level)
        {
            if (this._IsTransStarted == false)
            {
                this.Command.Transaction = this.Connection.BeginTransaction(level);
                ///Check if transaction have been created.
                if (this.Command.Transaction == null)
                    throw new ArgumentNullException(DatabaseExtension.Properties.Resources.DatabaseTransactionException);

                this._IsTransStarted = true;
            }
            return this.Command.Transaction;
        }
        /// <summary>
        /// Commit and close transaction
        /// </summary>
        public void EndTransaction()
        {
            if (this.Command.Transaction != null)
                this.Command.Transaction.Commit();
            this._IsTransStarted = false;
        }
        /// <summary>
        /// Rollback and close transaction
        /// </summary>
        public void RollbackTransaction()
        {
            if (this.Command.Transaction != null)
                this.Command.Transaction.Rollback();
            this._IsTransStarted = false;
        }
        #endregion
        #region SQL Parameters
        /// <summary>
        /// Add new Parameter to the database.
        /// </summary>
        /// <param name="param">Sql Parameter</param>
        private void AddParam(DbParameter param)
        {
            if (this.Command.Parameters.Count > this.MaxParamaterSize)
                throw new ArgumentOutOfRangeException(String.Format(DatabaseExtension.Properties.Resources.DatabaseParameterLengthExeededException, this.MaxParamaterSize));
            this.Command.Parameters.Add(param);
        }
        /// <summary>
        /// Create a DbParameter and save it in the parameter list.
        /// </summary>
        /// <param name="name">Parameter name</param>
        /// <param name="value">Parameter value</param>
        /// <param name="type">Parameter Type, depend on Database Type</param>
        /// <param name="isNullable">can parameter be null</param>
        /// <exception cref="ArgumentException">if type is not comform to the database type.</exception>
        public DbParameter SetSqlParameter(String name, Object value, Object type, Boolean isNullable, int Size = 0)
        {
            DbParameter param = null;
            param = this._Configurations.CreateParameter(name, value, type, isNullable, Size);
            this.AddParam(param);
            return param;
        }
        /// <summary>
        /// Create new SQL Parameter. No Sql Type reguired, since the type will be autocasted by value type.
        /// </summary>
        /// <param name="name">Parameter name</param>
        /// <param name="value">Parameter Value</param>
        public DbParameter SetSqlParameter(String name, Object value, int Size = 0)
        {
            var result = this._Configurations.CreateParameter(name, value, false, Size);
            this.AddParam(result);
            return result;
        }
        /// <summary>
        /// Create new SQL Parameter.
        /// </summary>
        /// <param name="name">Parameter name</param>
        /// <param name="value">Parameter Value</param>
        /// <param name="type">Sql Type</param>
        /// <param name="isNullable">if is nullable then true, else false</param>
        public DbParameter SetSqlParameter(String name, Object value, System.Data.DbType type, Boolean isNullable, int Size = 0)
        {
            return this.SetSqlParameter(name, value, (Object)type, isNullable, Size);
        }
        /// <summary>
        /// Create new SQL Parameter. Call SetSqlParameter(String, Object, System.Data.SqlDbType, true)
        /// </summary>
        /// <param name="name">Parameter name</param>
        /// <param name="value">Parameter Value</param>
        /// <param name="type">Sql Type</param>
        public DbParameter SetSqlParameter(String name, Object value, System.Data.DbType type, int Size = 0)
        {
            return this.SetSqlParameter(name, value, type, true, Size);
        }
        /// <summary>
        /// Set predefined SQL Parameter.
        /// </summary>
        /// <param name="name">Sql Parameter</param>
        public void SetSqlParameter(DbParameter param)
        {
            if (this.Actions.HasFlag(DbInterfaceActions.CheckDbParameter) == true)
                this._Configurations.CheckParameterOnDatabaseType(param);
            this.AddParam(param);
        }
        #endregion
        #region SQL Execute
        /// <summary>
        /// Creates a prepared version of the command on an instance of SQL Server (Increase Performance output).
        /// </summary>
        public void CommandPrepare()
        {
            this.Command.Prepare();
        }
        /// <summary>
        /// Execute Query with specific operation.
        /// </summary>
        /// <param name="query">Query String</param>
        /// <param name="operation">Operation Type.</param>
        /// <returns>Object, depend on operation.</returns>
        private Object SqlExecuteQuery(System.String query, SqlOperation operation)
        {
            Object result = new Object();
            this.Command.CommandText = query;
            this.Connect();

            if (this.Actions.HasFlag(DbInterfaceActions.UsePrepare) == true)
                this.CommandPrepare();

            switch (operation)
            {
                ///Return number of affected rows by operations Update/Delete/Insert.
                case SqlOperation.ExecuteNonQuery:
                    result = this.Command.ExecuteNonQuery();
                    break;

                ///Get first line only
                case SqlOperation.ExecuteScalar:
                    result = this.Command.ExecuteScalar();
                    break;

                ///Load data from adapater into dataset.
                case SqlOperation.DataAdapter:
                    result = this.GetDataSet();
                    break;
                ///Execute Reader... note if behavior is set to default, this is equivalent to for calling ExecuteReader().
                case SqlOperation.ExecuteReader:
                    result = this.Command.ExecuteReader(this.ExecuteReaderBehavior);
                    break;
                ///not implemented exception.
                default:
                    throw new NotImplementedException();
            }
            if (this.Actions.HasFlag(DbInterfaceActions.ResetParameters) == true)
            {
                this.Command.Parameters.Clear();
            }
            if (this.Actions.HasFlag(DbInterfaceActions.AutoClose) == true)
                this.Close();
            return result;
        }
        /// <summary>
        /// Execute Query and return it as DataSet
        /// </summary>
        /// <param name="query">SQL Query</param>
        /// <returns>DataSet</returns>
        public System.Data.DataSet SqlExecuteToDataSet(System.String query)
        {
            return this.SqlExecuteQuery(query, SqlOperation.DataAdapter) as System.Data.DataSet;
        }
        /// <summary>
        /// Execute Query and return number of affected rows by operations Update/Delete/Insert.
        /// </summary>
        /// <param name="query">Query String</param>
        /// <returns></returns>
        public int SqlExecuteNonQuery(System.String query)
        {
            return (int)this.SqlExecuteQuery(query, SqlOperation.ExecuteNonQuery);
        }
        /// <summary>
        /// Execute Sql Query that return only first column of the first row.
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public Object SqlExecuteScalar(System.String query)
        {
            return this.SqlExecuteQuery(query, SqlOperation.ExecuteScalar);
        }
        /// <summary>
        /// Execute SQL Query and fill the data adapter.
        /// </summary>
        /// <returns></returns>
        private System.Data.DataSet GetDataSet()
        {
            System.Data.DataSet result = new System.Data.DataSet();
            using (var adapter = this._Configurations.ClientFactory.CreateDataAdapter())
            {
                adapter.SelectCommand = this.Command;
                adapter.Fill(result);
            }
            return result;
        }
        #endregion
        #region Constructor
        /// <summary>
        /// Create new Instance of Database Interface with additional connection info.
        /// </summary>
        /// <param name="config">Connection info configuration.</param>
        public DbInterface(ConnectionInfo config)
        {
            this.ExecuteReaderBehavior = System.Data.CommandBehavior.Default;
            this.MaxParamaterSize = 1000;
            this.Actions = DbInterfaceActions.Default;
            this._Configurations = config;
        }

        private DbInterface()
            : this(null)
        {
        }
        #endregion
        #region Dispose
        public void Dispose()
        {
            this.Dispose(true);
            GC.SuppressFinalize(this);
        }
        ~DbInterface()
        {
            this.Dispose(false);
        }
        private void Dispose(Boolean dispose)
        {
            if (dispose == true)
            {
                if (this._Command != null)
                    this._Command.Dispose();
                if (this._Connection != null)
                    this._Connection.Dispose();
                this._Configurations = null;
                this.Disposed = true;
            }
        }
        #endregion
    }
}
